Hello, and welcome to sorting SELECT statement results sets.
In this video, we will learn about some advanced techniques in retrieving data from
a relational database table and sorting how the result set displays.
At the end of this lesson,
you will be able to describe how to sort the result set by either ascending or
descending order and explain how to indicate which column to use for the sorting order.
The main purpose of a database management system is not just to store the data,
but also facilitate retrieval of the data.
In its simplest form,
a select statement is select * from table name.
Based on our simplified library database model, in the table book,
select * from book gives a result set of four rows.
All the data rows for all columns in the table book are displayed.
We can choose to list the book titles only as shown in this example,
select title from book.
However, the order does not seem to be in any order.
Displaying the results set in
alphabetical order would make the result set more convenient.
To do this, we use the "order by" clause.
To display the result set in alphabetical order,
we add the order by clause to the select statement.
The order by clause is used in a query to sort the result set by a specified column.
In this example, we have used order by on the column title to sort the result set.
By default, the result set is sorted in ascending order.
In this example, the result set is sorted in alphabetical order by book title.
To sort in descending order, use the key word" desc."
The result set is now sorted according to the column specified,
which is title, and is sorted in descending order.
Notice the order of the first three rows.
The first three words of the title are the same,
so the sorting starts from the point where the characters differ.
Another way of specifying the sort column is to indicate the column sequence number.
In this example, select title, pages from book
order by two, indicates the column sequence number in the query for the sorting order.
Instead of specifying the column name pages,
the number two is used.
In the select statement, the second column specified in the column list is pages,
so the sort order is based on the values in the pages column.
In this case, the pages column indicates the number of pages in the book.
As you can see, the result set is in ascending order by number of pages.
Now you can describe how to sort the result set by either ascending or
descending order, and explain how to indicate which column to use for the sorting order.
Thanks for watching this video.